package test.DevilKeys.db;

import java.util.ArrayList;
import java.util.HashMap;

import test.DevilKeys.entity.User;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

	public final static String DB_NAME = "devil_keys.db";
	
	public final static String DB_TABLENAME = "security_data";
	
	public final static int VERSION = 9;
	
	private static DBHelper instance = null;

	private SQLiteDatabase db;
	
	public static DBHelper getinstance(Context context) {
		if (instance == null) {
			instance = new DBHelper(context);
		}
		
		return instance;
	}
	
	public void openDatabase() {
		if (db == null) {
			db = this.getWritableDatabase();
		}
	}
	
	public DBHelper(Context context) {
		super(context, DB_NAME, null, VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		StringBuffer tableCreate = new StringBuffer();
		tableCreate.append("create table "+DB_TABLENAME+" ( _id integer primary key autoincrement,")
		          .append("title text,")
		          .append("extra_title text,")
		          .append("user_id text,")
		          .append("password text,")
		          .append("email text,")
		          .append("question text,")
		          .append("answer text,")
		          .append("extra_info text )");
		
		db.execSQL(tableCreate.toString());
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String sql = "drop table if exists "+DB_TABLENAME;
		db.execSQL(sql);
		onCreate(db);
	}
	
	public long save(User user) {
		openDatabase();
		ContentValues value = new ContentValues();
		value.put("title", user.title);
		value.put("extra_title", user.extra_title);
		value.put("user_id", user.user_id);
		value.put("password", user.password);
		value.put("email", user.email);
		value.put("question", user.question);
		value.put("answer", user.answer);
		value.put("extra_info", user.extra_info);
		
		return db.insert(DB_TABLENAME, null, value);
	}
	
	public void modify(User user) {
		ContentValues value = new ContentValues();
		value.put("title", user.title);
		value.put("extra_title", user.extra_title);
		value.put("user_id", user.user_id);
		value.put("password", user.password);
		value.put("email", user.email);
		value.put("question", user.question);
		value.put("answer", user.answer);
		value.put("extra_info", user.extra_info);
		
		db.update(DB_TABLENAME, value, "_id=?", new String[]{String.valueOf(user._id)});
	}
	
	public void delete(int _id) {
		db.delete(DB_TABLENAME, "_id=?", new String[]{String.valueOf(_id)});
	}
	
	public void deleteMarked(ArrayList<Integer> deleteId) {
		StringBuffer strDeleteId = new StringBuffer();
		strDeleteId.append("_id=");
		for (int i=0; i<deleteId.size(); ++i) {
			if (i != deleteId.size()-1) {
				strDeleteId.append(deleteId.get(i)+" or _id=");
			}
			else {
				strDeleteId.append(deleteId.get(i));
			}
		}
		
		db.delete(DB_TABLENAME, strDeleteId.toString(), null);
		System.out.println(strDeleteId.toString());
	}
	
	public void deleteAll() {
		db.delete(DB_TABLENAME, null, null);
	}
	
	public ArrayList getUserList() {
		openDatabase();
		Cursor cursor = db.query(DB_TABLENAME, null, null, null, null, null, null);
		ArrayList list = new ArrayList();
		while (cursor.moveToNext()) {
			HashMap map = new HashMap();
			map.put("_id", cursor.getInt(cursor.getColumnIndex("_id")));
			map.put("title", cursor.getString(cursor.getColumnIndex("title")));
			map.put("extra_title", cursor.getString(cursor.getColumnIndex("extra_title")));
			map.put("user_id", cursor.getString(cursor.getColumnIndex("user_id")));
			map.put("password", cursor.getString(cursor.getColumnIndex("password")));
			map.put("email", cursor.getString(cursor.getColumnIndex("email")));
			map.put("question", cursor.getString(cursor.getColumnIndex("question")));
			map.put("answer", cursor.getString(cursor.getColumnIndex("answer")));
			map.put("extra_info", cursor.getString(cursor.getColumnIndex("extra_info")));
			list.add(map);
		}
		
		return list;
	}
	
	public ArrayList getAllUser() {
		Cursor cursor = db.query(DB_TABLENAME, null, null, null, null, null, null);
		ArrayList list = new ArrayList();
		while (cursor.moveToNext()) {
			HashMap map = new HashMap();
			map.put("_id", cursor.getInt(cursor.getColumnIndex("_id")));
			map.put("title", cursor.getString(cursor.getColumnIndex("title")));
			map.put("extra_title", cursor.getString(cursor.getColumnIndex("extra_title")));
			map.put("user_id", cursor.getString(cursor.getColumnIndex("user_id")));
			map.put("password", cursor.getString(cursor.getColumnIndex("password")));
			map.put("email", cursor.getString(cursor.getColumnIndex("email")));
			map.put("question", cursor.getString(cursor.getColumnIndex("question")));
			map.put("answer", cursor.getString(cursor.getColumnIndex("answer")));
			map.put("extra_info", cursor.getString(cursor.getColumnIndex("extra_info")));
			list.add(map);
		}
		
		return list;
	}
	
	public ArrayList getUserCondition(String condition) {
		ArrayList list = new ArrayList();
		String strSelection = "";
		String sql = "select * from " + DB_TABLENAME + " where 1=1 and (title like '%" + condition + "%' " +
				"or extra_title like '%" + condition + "%')" + strSelection;
		Cursor cursor = db.rawQuery(sql, null);
		
		while (cursor.moveToNext()) {
			HashMap map = new HashMap();
			map.put("_id", cursor.getInt(cursor.getColumnIndex("_id")));
			map.put("title", cursor.getString(cursor.getColumnIndex("title")));
			map.put("extra_title", cursor.getString(cursor.getColumnIndex("extra_title")));
			map.put("user_id", cursor.getString(cursor.getColumnIndex("user_id")));
			map.put("password", cursor.getString(cursor.getColumnIndex("password")));
			map.put("email", cursor.getString(cursor.getColumnIndex("email")));
			map.put("question", cursor.getString(cursor.getColumnIndex("question")));
			map.put("answer", cursor.getString(cursor.getColumnIndex("answer")));
			map.put("extra_info", cursor.getString(cursor.getColumnIndex("extra_info")));
			list.add(map);
		}
		
		return list;
	}

}
